package com.hulu.yec.sys.user.service;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.Validate;

import com.hulu.yec.common.Constant;
import com.hulu.yec.common.PageModel;
import com.hulu.yec.common.annotation.Column;
import com.hulu.yec.common.exception.DaoRuntimeException;
import com.hulu.yec.common.util.DBUtil;
import com.hulu.yec.common.util.DateUtil;
import com.hulu.yec.common.util.LogUtil;
import com.hulu.yec.sys.user.entity.SearchCondition;
import com.hulu.yec.sys.user.entity.SysUserEntity;

/**
 * 登录
 * 
 * @author hf 2016年3月6日
 */
public class SysUserDao {

	public int getClassId(String className) {
		Validate.notBlank(className, "className" + Constant.VALIDATE_NOT_NULL);
		String sql = "SELECT id FROM sys_class WHERE name='" + className + "'";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int classId = 0;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();

			resultSet.next();
			classId = resultSet.getInt(1);
		} catch (SQLException e) {
			LogUtil.i(Constant.LOGGER, "查询classid出现sql错误");
			throw new DaoRuntimeException("查询classid出现sql错误");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
		return classId;
	}

	/**
	 * 通过角色名字得到角色id
	 * 
	 * @param roleName
	 * @return
	 */
	public int getRoldId(String roleName) {
		Validate.notBlank(roleName, "roleName" + Constant.VALIDATE_NOT_NULL);
		String sql = "SELECT id FROM sys_role WHERE name='" + roleName + "'";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int roleId = 0;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();

			resultSet.next();
			roleId = resultSet.getInt(1);
		} catch (SQLException e) {
			LogUtil.i(Constant.LOGGER, "查询角色id出现sql错误");
			throw new DaoRuntimeException("查询角色id出现sql错误");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
		return roleId;
	}

	/**
	 * 得到一个用户的角色id
	 * 
	 * @param id
	 * @return
	 */
	private int getRoleId(int id) {
		if (id != 0) {
			String sql = "SELECT role_id FROM sys_user WHERE id=" + id;
			Connection connection = null;
			PreparedStatement preparedStatement = null;
			ResultSet resultSet = null;
			int roleId = 0;
			try {
				connection = DBUtil.getConnection();
				preparedStatement = connection.prepareStatement(sql);
				resultSet = preparedStatement.executeQuery();

				resultSet.next();
				roleId = resultSet.getInt(1);
			} catch (SQLException e) {
				LogUtil.i(Constant.LOGGER, "查询角色id出现sql错误");
				throw new DaoRuntimeException("查询角色id出现sql错误");
			} finally {
				DBUtil.close(connection, preparedStatement, resultSet);
			}
			return roleId;
		} else {
			throw new DaoRuntimeException("id为0");
		}

	}

	/**
	 * 分页查询，返回一个PageModel
	 * 
	 * @param pageNo
	 * @param pageSize
	 * @param searchConidtion
	 * @return
	 */
	public PageModel<SysUserEntity> listSplitAll(int pageNo, int pageSize,
			SearchCondition searchConidtion) {
		List<Object> paramList = new ArrayList<>();
		boolean isExistClassName = StringUtils.isNoneBlank(searchConidtion
				.getClassName());
		boolean isExistName = StringUtils
				.isNoneBlank(searchConidtion.getName());
		boolean isExistRoleName = StringUtils.isNoneBlank(searchConidtion
				.getRoleName());
		boolean isExistState = StringUtils.isNotBlank(searchConidtion
				.getStateStr());
		if (!isExistClassName && !isExistName && !isExistState
				&& !isExistRoleName) {
		}

		StringBuilder sb = new StringBuilder();
		sb.append("SELECT u.id,u.code,u.name,u.password,u.sex,u.class_id, ");
		sb.append("u.phone,u.email,u.create_time,r.name AS 'sys_role.name', ");
		if (isExistClassName) {
			sb.append("c.name AS 'sys_class.name',");
		}
		sb.append("u.state,u.memo ");
		sb.append(" FROM sys_user u,sys_role r ");
		if (isExistClassName) {
			sb.append(",sys_class c ");
		}
		sb.append("WHERE u.role_id=r.id");
		if (isExistClassName) {
			sb.append(" AND u.class_id=c.id AND c.name=? ");
			paramList.add(searchConidtion.getClassName());
		}
		if (isExistName) {
			sb.append(" AND u.name LIKE '%" + searchConidtion.getName() + "%' ");
		}
		if (isExistRoleName) {
			sb.append(" AND r.name=? ");
			paramList.add(searchConidtion.getRoleName());
		}
		if (isExistState) {
			sb.append(" AND u.state=?");
			paramList.add(searchConidtion.getState());
		} else {
			sb.append(" AND u.state!=2");
		}
		sb.append(" ORDER BY u.id DESC");
		sb.append(" LiMIT ?,?");
		paramList.add((pageNo - 1) * pageSize);
		paramList.add(pageSize);

		PageModel<SysUserEntity> pageModel = getPageModelEntity(
				SysUserEntity.class, pageNo, pageSize, sb.toString(), paramList);
		// 不存在班级名称，去循环查出班级名称
		if (!isExistClassName) {
			setAllClassName(pageModel);
		}
		return pageModel;
	}

	public <T> PageModel<T> getPageModelEntity(Class<T> clazz, int pageNo,
			int pageSize, String sql, List<Object> params) {
		Validate.notNull(clazz, "clazz" + Constant.VALIDATE_NOT_NULL);
		Validate.notNull(pageNo, "页数" + Constant.VALIDATE_NOT_NULL);
		Validate.notNull(pageSize, "页面大小" + Constant.VALIDATE_NOT_NULL);
		Validate.notBlank(sql, "sql语句" + Constant.VALIDATE_NOT_NULL);
		PageModel<T> pageModel = new PageModel<T>();
		String countSql = "SELECT COUNT(*) "
				+ sql.substring(sql.indexOf("FROM"), sql.indexOf("ORDER BY"));
		LogUtil.i(Constant.LOGGER, "countsql=" + countSql);
		LogUtil.i(Constant.LOGGER,
				"list=" + params.subList(0, params.size() - 2));
		pageModel.setList(excuteQuery(clazz, sql, params));
		pageModel.setAllRecords(getAllCount(countSql,
				params.subList(0, params.size() - 2)));
		pageModel.setPageNo(pageNo);
		pageModel.setPageSize(pageSize);
		return pageModel;
	}

	/**
	 * 数据库查询
	 * @param clazz
	 * @param sql
	 * @param param
	 * @return
	 */
	public <T> List<T> excuteQuery(Class<T> clazz, String sql, Object... param) {
		return excuteQuery(clazz, sql, Arrays.asList(param));
	}

	/**
	 * 数据库查询
	 * 
	 * @param clazz
	 * @param sql
	 * @param params
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	private <T> List<T> excuteQuery(Class<T> clazz, String sql,
			List<Object> params) {
		List<T> list = new ArrayList<T>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			DBUtil.setParameter(params, preparedStatement);
			resultSet = preparedStatement.executeQuery();
			ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
			int count = resultSetMetaData.getColumnCount();
			while (resultSet.next()) {
				// 构建一个T类型的实体对象
				T entity = clazz.newInstance();
				// 循环resultSet字段
				for (int i = 0; i < count; i++) {
					String labelName = resultSetMetaData.getColumnLabel(i + 1);
					// 得到所有的属性(这里不能使getFields（）——不能访问私有)
					Field[] fields = clazz.getDeclaredFields();
					// 循环所有的属性
					for (Field field : fields) {
						field.setAccessible(true);
						// 查找属性对应得类型
						Class<?> type = field.getType();
						String fieldName = field.getName();
						// 判断是否被注解修饰
						if (field.isAnnotationPresent(Column.class)) {
							Column column = field.getAnnotation(Column.class);
							// 注解的值
							String columnValue = column.value();
							// 别名和注解相等
							if (columnValue.equals(labelName)) {
								// 设置值
								if (type.equals(int.class)
										|| type.equals(Integer.class)) {
									field.set(entity,
											resultSet.getInt(labelName));
								} else if (type.equals(String.class)) {
									field.set(entity,
											resultSet.getString(labelName));
								} else if (type.equals(BigDecimal.class)) {
									field.set(entity,
											resultSet.getBigDecimal(labelName));
								} else if (type.equals(Date.class)) {
									field.set(entity,
											resultSet.getTimestamp(labelName));
								}
								break;
							}
						} else if (fieldName.equals(labelName)) {
							if (type.equals(int.class)
									|| type.equals(Integer.class)) {
								field.set(entity, resultSet.getInt(labelName));
							} else if (type.equals(String.class)) {
								field.set(entity,
										resultSet.getString(labelName));
							} else if (type.equals(BigDecimal.class)) {
								field.set(entity,
										resultSet.getBigDecimal(labelName));
							} else if (type.equals(Date.class)) {
								field.set(entity,
										resultSet.getTimestamp(labelName));
							}
							break;
						}

					}
				}
				list.add(entity);
			}

		} catch (SQLException e) {
			LogUtil.e(Constant.LOGGER, "数据库查询失败！", e);
			throw new DaoRuntimeException("数据库查询失败！");
		} catch (InstantiationException | IllegalAccessException e) {
			LogUtil.e(Constant.LOGGER, "SysDao层excuteQuery数据查询失败", e);
			throw new RuntimeException("数据查询失败");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
		return list;
	}

	/**
	 * 循环去查找班级名称，没有不管，有的查询并赋值
	 * 
	 * @param pageModel
	 */
	private void setAllClassName(PageModel<SysUserEntity> pageModel) {
		List<SysUserEntity> userList = pageModel.getList();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "SELECT name FROM sys_class WHERE id = ?";
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < userList.size(); i++) {
				if (userList.get(i).getClassId() == 0) {
					continue;
				}
				preparedStatement.setInt(1, userList.get(i).getClassId());
				resultSet = preparedStatement.executeQuery();
				while (resultSet.next()) {
					userList.get(i).setClassName(resultSet.getString("name"));
				}
			}
		} catch (SQLException e) {
			LogUtil.e(Constant.LOGGER, "班级名称查询出现sql异常！", e);
			throw new DaoRuntimeException("班级名称查询出现sql异常！");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 得到所有的班级名称
	 * 
	 * @return
	 */
	public List<String> getClassList() {
		String sql = "SELECT name FROM sys_class";
		return getSingleList(String.class, sql);
	}

	/**
	 * 得到所有的角色名
	 * 
	 * @return
	 */
	public List<String> getRoleList() {
		String sql = "SELECT name FROM sys_role";
		return getSingleList(String.class, sql);
	}

	/**
	 * 得到所有的账号
	 * 
	 * @return
	 */
	public List<String> getCodeList(){
		String sql = "SELECT code FROM sys_user";
		return getSingleList(String.class, sql);
	}
	
	/**
	 * 获取所有电话
	 * 
	 * @return
	 */
	public List<String> getPhoneList(){
		return getSingleList(String.class, "SELECT phone FROM sys_user");
	}
	/**
	 * 获取所有邮箱
	 * 
	 * @return
	 */
	public List<String> getEmailList(){
		return getSingleList(String.class, "SELECT email FROM sys_user");
	}
	/**
	 * 通过id得到此人的用户状态
	 * 
	 * @param id
	 * @return
	 */
	public int geStateById(int id) {
		String sql = "SELECT state FROM sys_user WHERE id=?";
		return getUniqueResult(SysUserEntity.class, sql, id).getState();
	}

	/**
	 * 改变用户状态
	 * 
	 * @param state
	 * @param id
	 * @return
	 */
	public int changeState(int state, int id) {
		String sql = "UPDATE sys_user SET state = ? WHERE id = ?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, state);
			preparedStatement.setInt(2, id);
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			LogUtil.e(Constant.LOGGER, "改变用户状态失败！", e);
			throw new DaoRuntimeException("改变用户状态失败！");
		} finally {
			DBUtil.close(connection, preparedStatement);
		}
	}

	/**
	 * 查询出一列基本数据集合
	 * 
	 * @param sql
	 * @param paramList
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> getSingleList(Class<T> clazz, String sql,
			List<Object> paramList) {
		List<T> list = new ArrayList<>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			DBUtil.setParameter(paramList, preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Object obj = resultSet.getObject(1);
				list.add((T) obj);
			}
		} catch (SQLException e) {
			LogUtil.e(Constant.LOGGER, "DBUtil查询一列字符串数据SQL错误", e);
			throw new DaoRuntimeException("DBUtil查询一列字符串数据SQL错误");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
		return list;
	}

	/**
	 * 查询出一列基本数据集合
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public <T> List<T> getSingleList(Class<T> clazz, String sql,
			Object... params) {
		return getSingleList(clazz, sql, Arrays.asList(params));
	}

	/**
	 * 查询：返回单一对象
	 * 
	 * @param clazz
	 * @param sql
	 * @param paramList
	 * @return
	 */
	public <T> T getUniqueResult(Class<T> clazz, String sql,
			List<Object> paramList) {
		Validate.notNull(clazz, "clazz" + Constant.VALIDATE_NOT_NULL);
		Validate.notBlank(sql, "sql" + Constant.VALIDATE_NOT_BLANK);

		List<T> list = excuteQuery(clazz, sql, paramList);
		if (list.isEmpty()) {
			return null;
		} else {
			return list.get(0);
		}
	}

	/**
	 * 查询：返回单一对象
	 * 
	 * @param clazz
	 * @param sql
	 * @param paramList
	 * @return
	 */
	public <T> T getUniqueResult(Class<T> clazz, String sql, Object... params) {
		Validate.notNull(clazz, "clazz" + Constant.VALIDATE_NOT_NULL);
		Validate.notBlank(sql, "sql" + Constant.VALIDATE_NOT_BLANK);
		return getUniqueResult(clazz, sql, Arrays.asList(params));
	}

	/**
	 * 查询记录数，返回总记录的数量
	 * 
	 * @throws SQLException
	 */
	public static int getAllCount(String sql, List<Object> paramList) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			DBUtil.setParameter(paramList, preparedStatement);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt(1);
		} catch (SQLException e) {
			LogUtil.e(Constant.LOGGER, "SysDao查询总记录数出现Sql异常", e);
			throw new DaoRuntimeException("SysDao查询总记录数出现Sql异常");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 带参数的查询记录数，返回总记录的数量
	 * 
	 * @throws SQLException
	 */
	public static int executeQuery(String sql, Object... params)
			throws SQLException {
		return getAllCount(sql, Arrays.asList(params));
	}

	/**
	 * 获取要修改的用户信息
	 * 
	 * @param id
	 * @return
	 */
	public SysUserEntity getUserInfo(int id) {
		StringBuilder sb = new StringBuilder();
		int roleId = getRoleId(id);
		sb.append("SELECT u.code,u.name,u.sex,u.phone,");
		sb.append("u.email,r.name AS 'sys_role.name',u.state,");
		if (roleId == 3) {
			sb.append("c.name AS 'sys_class.name',");
		}
		sb.append("u.memo ");
		sb.append(" FROM sys_role r,sys_user u ");
		if (roleId == 3) {
			sb.append(" ,sys_class c ");
		}
		sb.append(" WHERE u.role_id=r.id ");
		if (roleId == 3) {
			sb.append(" AND u.class_id=c.id ");
		}
		sb.append(" AND u.id=?");
		LogUtil.i(Constant.LOGGER, "获取要修改的用户信息" + sb.toString());
		return getUniqueResult(SysUserEntity.class, sb.toString(), id);
	}

	/**
	 * 新增用户
	 * @param user
	 * @return
	 */
	public int addUser(SysUserEntity user) {
		
		String sql = "INSERT INTO sys_user VALUES(NULL,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		List<Object> paramList = new ArrayList<>();
		paramList.add(user.getCode());
		paramList.add(Constant.DEFAULT_PASSWORD);
		paramList.add(user.getName());
		paramList.add(user.getSex());
		paramList.add(user.getPhone());
		paramList.add(user.getEmail());
		paramList.add(DateUtil.format(user.getOperateTime()));
		paramList.add(user.getOperateUserId());
		paramList.add(DateUtil.format(user.getOperateTime()));
		paramList.add(user.getRoleId());
		paramList.add(user.getClassId());
		paramList.add(Constant.DEFAULT_STATE);
		paramList.add(user.getMemo());
		return updateQuery(sql, paramList);
	}

	/**
	 * 数据库更新
	 * 
	 * @param sql
	 * @param paramList
	 * @return
	 */
	private int updateQuery(String sql, List<Object> paramList) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			DBUtil.setParameter(paramList, preparedStatement);
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException("数据更新失败");
		} finally {
			DBUtil.close(connection, preparedStatement);
		}
	}

	/**
	 * 数据库更新
	 * 
	 * @param sql
	 * @param param
	 * @return
	 */
	private int updateQuery(String sql, Object... param) {
		return updateQuery(sql, Arrays.asList(param));
	}

	/**
	 * 更改用户信息
	 * 
	 * @param sysUserEntity
	 * @param id
	 * @return
	 */
	public boolean changeUserInfo(SysUserEntity sysUserEntity, int id) {
		StringBuffer sb = new StringBuffer();
		List<Object> list = new ArrayList<>();
		sb.append("UPDATE sys_user SET name=?");
		list.add(sysUserEntity.getName());
		sb.append(",sex=?,phone=?,email=?,operate_time=?,");
		list.add(sysUserEntity.getSex());
		list.add(sysUserEntity.getPhone());
		list.add(sysUserEntity.getEmail());
		list.add(DateUtil.format(new Date()));
		sb.append("operate_user_id=?,role_id=?,");
		list.add(sysUserEntity.getId());
		list.add(sysUserEntity.getRoleId());
		if (sysUserEntity.getRoleId() == 3) {
			sb.append("class_id=?,");
			list.add(sysUserEntity.getClassId());
		}else{
			sb.append("class_id=null,");
		}
		sb.append("state=?");
		list.add(sysUserEntity.getState());
		if(!StringUtils.isNoneBlank(sysUserEntity.getMemo())){
			sb.append(",memo=null ");
		}else{
			sb.append(",memo=?");
			list.add(sysUserEntity.getMemo());
		}
		sb.append(" WHERE id=?");
		list.add(id);
		System.out.println(sb.toString());
		System.out.println(list);
		return updateQuery(sb.toString(), list)>0;
	}

	/**
	 * 用户重置密码
	 * 
	 * @param id
	 */
	public void resetPass(int id) {
		String sql = "UPDATE sys_user SET password=123456 WHERE id = " + id;
		updateQuery(sql);
	}

	/**
	 * 得到用户密码
	 * 
	 * @param id
	 * @return
	 */
	public String getPassword(int id) {
		String sql = "SELECT password FROM sys_user WHERE id=" + id;
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getString(1);
		} catch (SQLException e) {
			LogUtil.e(Constant.LOGGER, "SysDao查询用户密码出现Sql异常", e);
			throw new DaoRuntimeException("SysDao查询用户密码出现Sql异常");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 判断账号唯一
	 * 
	 * @param code
	 * @return
	 */
	public boolean checkCode(String code) {
		List<String> codeList=getCodeList();
		for (String getCode : codeList) {
			if(code.equals(getCode)){
				return false;
			}
		}
		return true;
	}

	/**
	 * 判断电话的唯一性
	 * 
	 * @param phone
	 * @return
	 */
	public boolean checkPhone(String phone) {
		List<String> phoneList=getPhoneList();
		for (String getPhone : phoneList) {
			if(phone.equals(getPhone)){
				return false;
			}
		}
		return true;
	}

	/**
	 * 判断邮箱的唯一性
	 * 
	 * @param email
	 * @return
	 */
	public boolean checkEmail(String email) {
		List<String> emailList=getEmailList();
		for (String getEmail : emailList) {
			if(email.equals(getEmail)){
				return false;
			}
		}
		return true;
	}

}
